﻿// =================================================================== 
// 项目说明
//====================================================================
// 
// 文件： SYS_SHORTCUT.cs
// 项目名称：

// 创建时间：2013/3/19
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLib.Model;
using CommonLib.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  .
    /// </summary>
    public partial class SYS_SHORTCUTDAL : ISYS_SHORTCUTDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取SYS_SHORTCUT表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的


        private const string QueryDataString = " SELECT USER_ID, MENU_NO, MENU_NAME,(SELECT A.MENU_NAME FROM SYS_MENU A WHERE A.MENU_NO=(SELECT B.PARENT_MENU_NO FROM SYS_MENU B WHERE B.MENU_NO=SYS_SHORTCUT.MENU_NO)) PARENTMENU,(SELECT B.SYSTEM_NAME FROM SYS_SUBSYSTEM B WHERE B.SUB_SYSTEM=(SELECT A.SUB_SYSTEM FROM SYS_MENU A WHERE A.MENU_NO=SYS_SHORTCUT.MENU_NO)) SUB_SYSTEM FROM SYS_SHORTCUT  ";


        #region	GetUserRoleList 得到数据表指定字段列表的所有记录，可排序，
        /// <summary>
        /// 得到数据表SYS_SHORTCUT所有记录，可排序

        /// </summary>
        /// <param name="sWhere">条件字符串</param>
        /// <param name="sOder">排序字段</param>
        /// <returns>DataSet</returns>
        public DataSet GetUserRoleList(string sWhere, string sOder)
        {
            string sqlStr = " SELECT ROLE_ID, ROLE_NAME FROM SYS_ROLES WHERE EXISTS(SELECT 1 FROM (SELECT B.ROLE_ID,A.USER_ID FROM SYS_OPERATOR_ROLE A INNER JOIN SYS_DEPT_ROLES B ON A.MASTER_ID=B.MASTER_ID) T WHERE SYS_ROLES.ROLE_ID = T.ROLE_ID AND T.USER_ID='" + sWhere + "') ";
            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }
        #endregion


        #region	GetRoleMenuList 得到数据表指定字段列表的所有记录，可排序，
        /// <summary>
        /// 得到数据表SYS_SHORTCUT所有记录，可排序

        /// </summary>
        /// <param name="sWhere">条件字符串</param>
        /// <param name="sOder">排序字段</param>
        /// <returns>DataSet</returns>
        public DataSet GetRoleMenuList(string sWhere, string sOder)
        {
            string sqlStr = " SELECT MENU_NO,MENU_NAME FROM SYS_MENU WHERE EXISTS (SELECT 1 FROM SYS_ROLE_FUNCTION A WHERE A.ROLE_ID in (" + sWhere + ") AND SYS_MENU.MENU_NO = A.MENU_NO AND SYS_MENU.IS_LEAF=1) ";
            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

        public DataSet GetShortMenu(string user_id)
        {
            string sqlStr = " SELECT F.MENU_NO,F.MENU_NAME,F.URL_ADDRESS,F.MENU_PARAMETER1,F.MENU_PARAMETER2,F.ENTRY_PARAMETER,F.MENU_PARAMETER3,F.EXEC_TYPE,MIN(E.READONLY) R_ONLY FROM SYS_SHORTCUT A,SYS_OPERATOR_ROLE B,"
                         + " SYS_DEPT_ROLES C,SYS_ROLES D,SYS_ROLE_FUNCTION E,SYS_MENU F  WHERE A.USER_ID='" + user_id + "'"
                         + " AND A.MENU_NO=E.MENU_NO AND A.USER_ID=B.USER_ID AND B.MASTER_ID=C.MASTER_ID "
                         + " AND C.ROLE_ID=D.ROLE_ID AND D.ROLE_ID=E.ROLE_ID AND E.MENU_NO=F.MENU_NO "
                         + " GROUP BY F.MENU_NO,F.MENU_NAME,F.URL_ADDRESS,F.MENU_PARAMETER1,F.MENU_PARAMETER2,F.ENTRY_PARAMETER,F.MENU_PARAMETER3,F.EXEC_TYPE ORDER BY F.MENU_NO ";
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

        #endregion
    }
}
